package com.piece.admin.controller.system;

import cn.smallbun.screw.core.engine.EngineFileType;
import com.piece.core.db.util.ScrewUtil;
import com.piece.core.framework.annotation.permission.AclPermission;
import com.piece.core.framework.annotation.permission.SubmitPermission;
import com.piece.core.framework.constant.ExceptionConstants;
import com.piece.core.db.util.DataBaseUtil;
import com.piece.core.db.util.execute.ExecuteParam;
import com.piece.core.db.util.execute.ExecuteResult;
import com.piece.core.db.util.execute.SqlExecutor;
import com.piece.core.framework.constant.FrameWorkConstants;
import com.piece.core.framework.enums.StatusType;
import com.piece.core.framework.enums.sql.SqlKey;
import com.piece.core.framework.support.bootstrap.DataTable;
import com.piece.core.framework.support.bootstrap.Order;
import com.piece.core.framework.support.bootstrap.QueryTable;
import com.piece.core.framework.support.bootstrap.Valid;
import com.piece.core.framework.support.convert.Convert;
import com.piece.core.framework.support.response.AjaxResponse;
import com.piece.core.framework.support.response.Select;
import com.piece.core.framework.util.collection.CollectionUtil;
import com.piece.core.framework.util.collection.MapUtil;
import com.piece.core.framework.util.basic.ConfigUtil;
import com.piece.core.framework.util.basic.I18nUtil;
import com.piece.core.framework.util.string.StringUtil;
import com.piece.core.framework.util.file.FileUtil;
import com.piece.core.web.controller.FrameWorkController;
import com.piece.file.client.FileClientFactory;
import com.piece.file.model.TbSysFile;
import com.piece.file.service.TbSysFileService;
import com.piece.module.file.util.excel.ExcelParam;
import com.piece.module.file.util.excel.ExcelUtil;
import com.piece.module.generator.model.GenTable;
import com.piece.module.generator.model.GenTableColumn;
import com.piece.module.generator.model.TbSysDb;
import com.piece.module.generator.model.TbSysDbQuery;
import com.piece.module.generator.service.GenService;
import com.piece.module.generator.service.GenTableService;
import com.piece.module.generator.service.TbSysDbQueryService;
import com.piece.module.generator.service.TbSysDbService;
import com.zaxxer.hikari.HikariDataSource;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiImplicitParam;
import io.swagger.annotations.ApiImplicitParams;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.cloud.context.config.annotation.RefreshScope;
import org.springframework.web.bind.annotation.*;
import javax.annotation.Resource;
import java.io.File;
import java.io.InputStream;
import java.util.*;
import java.util.stream.Collectors;

@Slf4j
@RefreshScope
@RestController
@Api(tags = {"数据源管理"})
@RequestMapping("/api/system/dbase")
public class SysDbController extends FrameWorkController {

    @Value("${spring.datasource.druid.master.driver-class-name:#{null}}")
    private String masterDriverClassName;

    @Value("${spring.datasource.druid.master.url:#{null}}")
    private String masterUrl;

    @Value("${spring.datasource.druid.master.username:#{null}}")
    private String masterUsername;

    @Value("${spring.datasource.druid.master.password:#{null}}")
    private String masterPassword;

    @Resource
    private TbSysDbService tbSysDbService;

    @Resource
    private TbSysDbQueryService tbSysDbQueryService;

    @Resource
    private GenService genService;

    @Resource
    private GenTableService genTableService;

    @Resource
    private FileClientFactory fileClientFactory;

    @Resource
    private TbSysFileService tbSysFileService;

    @PostMapping("/list")
    @AclPermission(code = "tool:db:view")
    @ApiOperation(value = "查询数据源列表")
    @ApiImplicitParam(value = "查询条件", dataTypeClass = QueryTable.class, type = "body")
    public List list() {
        QueryTable queryTable = initTable();
        DataTable dataTable = tbSysDbService.findPage(queryTable);
        return dataTable.getData();
    }

    @PostMapping("/table")
    @AclPermission(code = "tool:db:view")
    @ApiOperation(value = "查询数据源分页数据")
    @ApiImplicitParam(value = "查询条件", dataTypeClass = QueryTable.class, type = "body")
    public AjaxResponse table() {
        QueryTable queryTable = initTable();
        DataTable dataTable = tbSysDbService.findPage(queryTable);
        return success(dataTable);
    }

    @GetMapping("/load")
    @AclPermission(code = "tool:db:view")
    @ApiOperation(value = "加载数据源表单数据")
    @ApiImplicitParam(name = "id", value = "数据源ID")
    public AjaxResponse load() {
        String dbKey = getRequest().getParameter("id");
        TbSysDb tbSysDb = new TbSysDb();
        if (StringUtil.isNotEmpty(dbKey)) {
            tbSysDb = tbSysDbService.findById(dbKey);
        }
        return success(tbSysDb);
    }

    @SubmitPermission
    @PostMapping("/save")
    @AclPermission(code = "tool:db:add,tool:db:edit")
    @ApiOperation(value = "保存数据源表单数据")
    @ApiImplicitParam(value = "数据源表单", dataTypeClass = TbSysDb.class, type = "body")
    public AjaxResponse save() {
        try {
            TbSysDb tbSysDb = tbSysDbService.findById(getRequest().getParameter("dbKey"));
            if (null == tbSysDb) {
                // 新增数据源
                tbSysDb = new TbSysDb();
                initDB(tbSysDb);
                tbSysDbService.insert(tbSysDb);
            } else {
                // 变更数据源
                initDB(tbSysDb);
                tbSysDbService.update(tbSysDb);
            }
            return success(I18nUtil.message(ExceptionConstants.SAVE_SUCCESS), null);
        } catch (Exception e) {
            return error(I18nUtil.message(ExceptionConstants.SAVE_FAILURE), e.getMessage());
        }
    }

    @SubmitPermission
    @PostMapping("/delete")
    @AclPermission(code = "tool:db:delete")
    @ApiOperation(value = "删除数据源")
    @ApiImplicitParam(name = "ids", value = "数据源ID集合")
    public AjaxResponse delete() throws Exception {
        String ids = getRequest().getParameter("ids");
        if (StringUtil.isNotEmpty(ids)) {
            tbSysDbService.delete(Convert.toStrArray(ids));
        }
        return success(I18nUtil.message(ExceptionConstants.DELETE_SUCCESS), null);
    }

    @GetMapping("/checkDbUnique")
    @AclPermission(code = "tool:db:view")
    @ApiOperation(value = "检查数据源是否重复")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "dbKey", value = "数据源Key"),
            @ApiImplicitParam(name = "url", value = "数据源地址")
    })
    public Valid checkDbUnique() {
        String dbKey = getRequest().getParameter("dbKey");
        String dbName = getRequest().getParameter("dbName");
        boolean result = true;
        Valid valid = new Valid();
        if (StringUtil.isNotEmpty(dbName)) {
            result = tbSysDbService.checkNameUnique(dbKey, dbName);
        } else if (StringUtil.isNotEmpty(dbKey)) {
            result = tbSysDbService.checkDbKeyUnique(dbKey);
        }

        valid.setValid(result);
        return valid;
    }

    @PostMapping("/select")
    @ApiOperation(value = "查询数据源下拉列表")
    @ApiImplicitParams({
            @ApiImplicitParam(value = "分页查询条件", dataTypeClass = Select.class, type = "body"),
            @ApiImplicitParam(name = "Q_dbName_S_LK", value = "数据源名称")
    })
    public AjaxResponse select() {
        String selected = getRequest().getParameter("selected");
        QueryTable queryTable = initSelect("Q_dbName_S_LK");
        Map<String, Object> param = queryTable.getParams();
        param.put("status", StatusType.NORMAL.getCode());
        queryTable.setParams(param);
        DataTable dataTable = tbSysDbService.findPage(queryTable);
        if (StringUtil.isNotEmpty(selected)) {
            selected = FrameWorkConstants.SPLIT_PARAM + selected + FrameWorkConstants.SPLIT_PARAM;
        }
        return success(tableToSelect(dataTable, selected, "db"));
    }

    private Select tableToSelect(DataTable dataTable, String selected, String type) {
        Select select = Select.builder().build();
        List<Object> tableData = dataTable.getData();
        List<Select.Entity> entityList = tableData.stream().map(data -> {
            if ("db".equals(type)) {
                TbSysDb db = (TbSysDb) data;
                return select.new Entity(Convert.toStr(db.getDbKey()), Convert.toStr(db.getDbName()));
            } else if ("db_query".equals(type)) {
                TbSysDbQuery query = (TbSysDbQuery) data;
                return select.new Entity(Convert.toStr(query.getGroupName()), Convert.toStr(query.getGroupName()) + "[" + Convert.toStr(query.getDbKey()) + "]");
            } else if ("dbTable".equals(type)) {
                GenTable table = (GenTable) data;
                return select.new Entity(Convert.toStr(table.getTableName()), Convert.toStr(table.getTableName()));
            }
            return null;
        }).filter(entity -> {
            if (StringUtil.isNotEmpty(selected)) {
                if (selected.indexOf(FrameWorkConstants.SPLIT_PARAM + entity.getId() + FrameWorkConstants.SPLIT_PARAM) != -1) {
                    return true;
                }
                return false;
            }
            return true;
        }).collect(Collectors.toList());
        select.setTotal(dataTable.getRecordsTotal());
        if ("db".equals(type)) {
            entityList.add(0, select.new Entity(FrameWorkConstants.DATASOURCE_MASTER, FrameWorkConstants.DATASOURCE_MASTER));
        }
        select.setData(entityList);
        return select;
    }

    @PostMapping("/{dbKey}/table")
    @AclPermission(code = "tool:db:exec")
    @ApiOperation(value = "查询数据源下数据表分页数据")
    @ApiImplicitParam(value = "查询条件", dataTypeClass = QueryTable.class, type = "body")
    public AjaxResponse table(@PathVariable String dbKey) {
        QueryTable queryTable = initTable();
        TbSysDb tbSysDb = tbSysDbService.findByDBKey(dbKey);
        if (null == tbSysDb) {
            tbSysDb = new TbSysDb();
            tbSysDb.setDbKey(FrameWorkConstants.DATASOURCE_MASTER);
            tbSysDb.setDriverClassName(ConfigUtil.getInstance().get("spring.datasource.druid.master.driver-class-name"));
        }
        String tableName = getRequest().getParameter("tableName");
        List<GenTable> tableList = (List<GenTable>) genService.genDBInfo(tbSysDb, tableName).get(0);
        // 移除过滤条件
        if (StringUtil.isNotEmpty(tableName)) {
            tableList.removeIf(table -> !table.getTableName().toLowerCase().contains(tableName.toLowerCase()));
        }
        // 移除已生成的表
        tableList.removeIf(table -> genTableService.checkExist(table.getDbKey(), table.getTableName()));

        DataTable dataTable = new DataTable(queryTable);
        dataTable.setData(tableList);
        return success(dataTable);
    }

    @PostMapping("/{dbKey}/select")
    @ApiOperation(value = "查询数据源下数据表下拉列表")
    @ApiImplicitParams({
            @ApiImplicitParam(value = "分页查询条件", dataTypeClass = Select.class, type = "body"),
            @ApiImplicitParam(name = "Q_dbName_S_LK", value = "数据源名称")
    })
    public AjaxResponse dbTableSelect(@PathVariable String dbKey) {
        String selected = getRequest().getParameter("selected");
        QueryTable queryTable = initSelect("Q_tableName_S_LK");
        TbSysDb tbSysDb = tbSysDbService.findByDBKey(dbKey);
        if (null == tbSysDb) {
            tbSysDb = new TbSysDb();
            tbSysDb.setDbKey(FrameWorkConstants.DATASOURCE_MASTER);
            tbSysDb.setDriverClassName(ConfigUtil.getInstance().get("spring.datasource.druid.master.driver-class-name"));
        }

        if (StringUtil.isNotEmpty(selected)) {
            selected = FrameWorkConstants.SPLIT_PARAM + selected + FrameWorkConstants.SPLIT_PARAM;
        }

        List<GenTable> tableList = (List<GenTable>) genService.genDBInfo(tbSysDb, null).get(0);
        String key = Convert.toStr(queryTable.getParams().get("Q_tableName_S_LK"));
        tableList = tableList.stream().filter(entity -> {
            if (StringUtil.isNotEmpty(key)) {
                if ((FrameWorkConstants.SPLIT_PARAM + entity.getTableName().toLowerCase() + FrameWorkConstants.SPLIT_PARAM).indexOf(key.toLowerCase()) != -1) {
                    return true;
                }
                return false;
            }
            return true;
        }).collect(Collectors.toList());
        DataTable dataTable = new DataTable(queryTable);
        dataTable.setRecordsTotal(tableList.size());
        List<GenTable> resultList = tableList.stream().skip(queryTable.getStart()).limit(queryTable.getLength()).collect(Collectors.toList());
        dataTable.setData(resultList);
        return success(tableToSelect(dataTable, selected, "dbTable"));
    }

    @PostMapping("/dbInfo")
    @AclPermission(code = "tool:db:exec")
    @ApiOperation(value = "查询数据源所有数据表和数据列")
    @ApiImplicitParam(name = "dbKey", value = "数据源Key")
    public AjaxResponse dbInfo() {
        String dbKey = getRequest().getParameter("dbKey");
        List<String> dbNames = new ArrayList<>();
        Map<String, List<GenTable>> tables = new HashMap<>();
        Map<String, List<GenTableColumn>> columns = new HashMap<>();
        if (StringUtil.isNotEmpty(dbKey) && !"master".equals(dbKey)) {
            TbSysDb tbSysDb = tbSysDbService.findByDBKey(dbKey);
            List result = genService.genDBInfo(tbSysDb, null);
            dbNames.add(dbKey);
            tables.put(dbKey, (List<GenTable>) result.get(0));
            columns.putAll((Map<? extends String, ? extends List<GenTableColumn>>) result.get(1));
        } else {
            TbSysDb master = new TbSysDb();
            master.setDbKey("master");
            master.setDriverClassName(masterDriverClassName);
            List result = genService.genDBInfo(master, null);
            dbNames.add(master.getDbKey());
            tables.put(master.getDbKey(), (List<GenTable>) result.get(0));
            columns.putAll((Map<? extends String, ? extends List<GenTableColumn>>) result.get(1));
        }
        Map<String, Object> results = new HashMap<>();
        results.put("dbNames", dbNames);
        results.put("tables", tables);
        results.put("columns", columns);
        return success(results);
    }

    @PostMapping("/query/select")
    @ApiOperation(value = "查询自定义查询下拉列表")
    @ApiImplicitParams({
            @ApiImplicitParam(value = "分页查询条件", dataTypeClass = Select.class, type = "body")
    })
    public AjaxResponse selectCustomQuery() {
        String selected = getRequest().getParameter("selected");
        QueryTable queryTable = initSelect("Q_groupName_S_LK");
        Map<String, Object> param = queryTable.getParams();
        param.put("sort", 1);
        queryTable.setParams(param);
        DataTable dataTable = tbSysDbQueryService.findPage(queryTable);
        if (StringUtil.isNotEmpty(selected)) {
            selected = FrameWorkConstants.SPLIT_PARAM + selected + FrameWorkConstants.SPLIT_PARAM;
        }
        return success(tableToSelect(dataTable, selected, "db_query"));
    }

    @GetMapping("/query/load")
    @AclPermission(code = "tool:db:view")
    @ApiOperation(value = "加载自定义查询表单数据")
    @ApiImplicitParam(name = "groupName", value = "查询所属分组名称")
    public AjaxResponse loadCustomQuery() {
        String groupName = getRequest().getParameter("groupName");
        return success(tbSysDbQueryService.findByCondition(MapUtil.builder(new HashMap<String, Object>())
                .put("groupName", groupName).build(),
                Arrays.asList(new Order("sort", SqlKey.ASC))));
    }

    @SubmitPermission
    @PostMapping("/query/save")
    @AclPermission(code = "tool:db:add,tool:db:edit")
    @ApiOperation(value = "保存自定义查询表单数据")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "groupName", value = "查询所属分组名称"),
            @ApiImplicitParam(name = "dbKey", value = "数据源Key"),
            @ApiImplicitParam(name = "index", value = "查询语句数量"),
            @ApiImplicitParam(name = "querySql_0", value = "查询语句")
    })
    public AjaxResponse saveCustomQuery() {
        int index = Convert.toInt(getRequest().getParameter("index"), 0);
        String dbKey = getRequest().getParameter("dbKey");
        dbKey = StringUtil.isEmpty(dbKey) ? "master" : dbKey;
        String groupName = getRequest().getParameter("groupName");

        try {
            if (StringUtil.isNotEmpty(groupName)) {
                tbSysDbQueryService.deleteByGroupName(groupName);
                TbSysDbQuery dbQuery = null;
                for (int i = 1; i <= index; i++) {
                    dbQuery = new TbSysDbQuery();
                    dbQuery.setDbKey(dbKey);
                    dbQuery.setGroupName(groupName);
                    dbQuery.setSql(Convert.toStr(getRequest().getParameter("querySql_" + i)));
                    dbQuery.setSort(i);
                    tbSysDbQueryService.insert(dbQuery);
                }
            }
            return success(I18nUtil.message(ExceptionConstants.SAVE_SUCCESS), null);
        } catch (Exception e) {
            return error(I18nUtil.message(ExceptionConstants.SAVE_FAILURE), e.getMessage());
        }
    }

    @SubmitPermission
    @PostMapping("/query/delete")
    @AclPermission(code = "tool:db:delete")
    @ApiOperation(value = "删除自定义查询")
    @ApiImplicitParam(name = "groupName", value = "查询所属分组名称")
    public AjaxResponse deleteCustomQuery() {
        String groupName = getRequest().getParameter("groupName");
        if (StringUtil.isNotEmpty(groupName)) {
            tbSysDbQueryService.deleteByGroupName(groupName);
        }
        return success(I18nUtil.message(ExceptionConstants.DELETE_SUCCESS), null);
    }

    @SubmitPermission
    @PostMapping("/execute/{executeId}")
    @AclPermission(code = "tool:db:exec")
    @ApiOperation(value = "执行SQL语句")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "executeId", value = "执行线程ID", type = "path"),
            @ApiImplicitParam(name = "dbKey", value = "数据源Key"),
            @ApiImplicitParam(name = "sql", value = "查询语句")
    })
    public AjaxResponse execute(@PathVariable String executeId) {
        try {
            String dbKey = getRequest().getParameter("dbKey");
            dbKey = StringUtil.isEmpty(dbKey) ? "master" : dbKey;
            String sql = getRequest().getParameter("sql");
            List<ExecuteResult> resultList = new LinkedList<>();
            if (StringUtil.isNotEmpty(sql)) {
                // 支持;分割的多个sql执行
                String[] sqlArr = sql.split(";");
                for (String sqlItem : sqlArr) {
                    if (StringUtil.isBlank(sqlItem)) {
                        continue;
                    }
                    //sqlItem = HtmlUtil.previewText(sqlItem);
                    try {
                        ExecuteParam executeParam = ExecuteParam.builder()
                                .dbKey(dbKey)
                                .executeId(executeId)
                                .sql(sqlItem)
                                .maxRows(1000)
                                .build();

                        ExecuteResult executeResult = SqlExecutor.getInstance().execute(executeParam);
                        resultList.add(executeResult);
                    } catch (Exception e) {
                        ExecuteResult executeResult = ExecuteResult.error(e.getMessage(), sqlItem);
                        resultList.add(executeResult);
                    }
                }
            }
            return success(resultList);
        } catch (Exception e) {
            return error(I18nUtil.message(ExceptionConstants.SERVER), e.getMessage());
        }
    }

    @SubmitPermission
    @PostMapping(value = "/cancel/{executeId}")
    @AclPermission(code = "tool:db:exec")
    @ApiOperation(value = "取消执行SQL语句")
    @ApiImplicitParam(name = "executeId", value = "执行线程ID", type = "path")
    public AjaxResponse cancel(@PathVariable String executeId) {
        SqlExecutor.getInstance().cancel(executeId);
        return success();
    }

    @SubmitPermission
    @PostMapping("/exportQuery")
    @AclPermission(code = "tool:db:exec")
    @ApiOperation(value = "导出查询SQL数据")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "dbKey", value = "数据源Key"),
            @ApiImplicitParam(name = "index", value = "查询语句数量"),
            @ApiImplicitParam(name = "querySql_0", value = "查询语句")
    })
    public AjaxResponse exportQuery() {
        try {
            int index = Convert.toInt(getRequest().getParameter("index"), 0);
            String dbKey = getRequest().getParameter("dbKey");
            dbKey = StringUtil.isEmpty(dbKey) ? "master" : dbKey;
            String fileId = getRequest().getParameter("fileId");
            String sql;
            Map<String, Object> rowMap;
            Map<String, List> columnMap;
            Map<String, List> excelMap = new HashMap<>();
            List<String> patternList;
            List<Map<String, Object>> resultList = null;
            TbSysFile excel = null;

            if (StringUtil.isNotEmpty(fileId)) {
                excel = tbSysFileService.findById(fileId);
                InputStream stream = fileClientFactory.get().downloadFile(excel);
                List<Map<String, Object>> readData = ExcelUtil.read(stream, ExcelParam.builder().build());
                readData.stream().forEach(map -> map.entrySet().forEach(entry -> {
                    if (null != excelMap.get(entry.getKey())) {
                        List result = excelMap.get(entry.getKey());
                        result.add(entry.getValue());
                        excelMap.put(entry.getKey(), result);
                    } else {
                        excelMap.put(entry.getKey(), new ArrayList(Arrays.asList(entry.getValue())));
                    }
                }));
            }

            for (int i = 1; i <= index; i++) {
                columnMap = new HashMap<>();
                sql = getRequest().getParameter("querySql_" + i);
                if (resultList != null) {
                    for (int j = 0; j < resultList.size(); j++) {
                        rowMap = resultList.get(j);
                        for (Map.Entry<String, Object> entry : rowMap.entrySet()) {
                            generateColumnList(columnMap, entry.getKey(), entry.getValue());
                        }
                    }
                }
                if (StringUtil.isNotEmpty(sql)) {
                    // 格式化查询SQL
                    patternList = StringUtil.extractMessageByRegular(sql, "\\{(.*?)\\}");
                    sql = formatSql(sql, patternList, columnMap, excelMap);
                    if (sql.toUpperCase().startsWith("SELECT")) {
                        resultList = DataBaseUtil.queryForList(dbKey, sql, null);
                    } else {
                        DataBaseUtil.execute(dbKey, sql);
                    }
                }
            }

            List<List<String>> titleList = new ArrayList<>();
            if (CollectionUtil.isNotEmpty(resultList)) {
                Map<String, Object> titleRow = resultList.get(0);
                for (String key : titleRow.keySet()) {
                    List<String> row_list = new ArrayList<>();
                    row_list.add(key);
                    titleList.add(row_list);
                }
            }
            ExcelParam param = ExcelParam.builder()
                    .sheetName("查询结果")
                    .headList(titleList)
                    .data(resultList)
                    .convertType(Map.class)
                    .convert(true)
                    .build();
            InputStream is = ExcelUtil.write(param);
            String fileName = "[查询结果]_" + FileUtil.generateUniqFileName(getRequest()) + ".xlsx";
            String filePath = fileClientFactory.getNative().writeToLocalStore(FileUtil.toByte(is), FileUtil.getTempPath(), fileName);

            // 删除临时文件
            if (null != excel) {
                fileClientFactory.get().deleteFile(excel);
            }

            return success(filePath);
        } catch (Exception e) {
            return error(I18nUtil.message(ExceptionConstants.DOWNLOAD_ERROR), e.getMessage());
        }
    }

    private void generateColumnList(Map<String, List> columnMap, String columnName, Object columnValue) {
        List list = new ArrayList();
        if (null != columnMap.get(columnName)) {
            list = columnMap.get(columnName);
        }
        list.add(columnValue);
        columnMap.put(columnName, list);
    }

    private String formatSql(String sql, List<String> patternList, Map<String, List> columnMap, Map<String, List> excelMap) {
        if (null == patternList || patternList.size() < 1) {
            return sql;
        }

        String replace;
        String[] pattern;
        List valueList;
        for (int i = 0; i < patternList.size(); i++) {
            replace = patternList.get(i);
            pattern = replace.split(FrameWorkConstants.SEPARATOR_KEY_VALUE);
            if ("excel".equals(pattern[0])) {
                valueList = excelMap.get(pattern[1]);
            } else {
                valueList = columnMap.get(pattern[1]);
            }

            if ("in".equals(pattern[2])) {
                sql = sql.replace("{" + replace + "}", "(" + StringUtil.convert2Sql(valueList.toArray()) + ")");
            } else {
                sql = sql.replace("{" + replace + "}", StringUtil.convert2Sql(valueList.toArray()));
            }
        }
        return sql;
    }

    @SubmitPermission
    @RequestMapping("/doc/{dbKey}/{type}")
    @AclPermission(code = "tool:db:view")
    @ApiOperation(value = "生成数据库文档")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "dbKey", value = "数据源", type = "path"),
            @ApiImplicitParam(name = "type", value = "文档类型", allowableValues="html,doc,md", type = "path")
    })
    public void exportDoc(@PathVariable String dbKey, @PathVariable String type) {
        String filePath = null;
        try {
            TbSysDb tbSysDb = null;
            if ("master".equals(dbKey)) {
                tbSysDb = new TbSysDb();
                tbSysDb.setDbKey("master");
                tbSysDb.setDriverClassName(masterDriverClassName);
                tbSysDb.setUrl(masterUrl);
                tbSysDb.setUsername(masterUsername);
                tbSysDb.setPassword(masterPassword);
            } else {
                tbSysDb = tbSysDbService.findById(dbKey);
            }
            EngineFileType fileType = null;
            switch (type) {
                case "doc":
                    fileType = EngineFileType.WORD;
                    break;
                case "md":
                    fileType = EngineFileType.MD;
                    break;
                case "html":
                default:
                    fileType = EngineFileType.HTML;
            }

            String fileName = ScrewUtil.DOC_FILE_NAME + "_" + FileUtil.generateUniqFileName(getRequest());
            List<TbSysFile> fileList = tbSysFileService.findByTableAndBusiness("Screw_" + tbSysDb.getDbKey(), type);
            if (CollectionUtils.isNotEmpty(fileList)) {
                InputStream inputStream = fileClientFactory.get().downloadFile(fileList.get(0));
                FileUtil.downloadForWeb(getRequest(), getResponse(), inputStream, fileName + fileType.getFileSuffix());
            } else {
                HikariDataSource dataSource = ScrewUtil.buildDataSource(tbSysDb.getDriverClassName(), tbSysDb.getUrl(),
                        tbSysDb.getUsername(), tbSysDb.getPassword());
                // 生成本地文件
                filePath = ScrewUtil.doExecute(dataSource, fileType, FileUtil.getTempPath(), fileName);
                File localFile = new File(filePath);
                TbSysFile file = fileClientFactory.get().writeToStore(FileUtil.readFileToByteArray(localFile),
                        "screw", localFile.getName(), localFile.length());
                file.setTable("Screw_" + tbSysDb.getDbKey());
                file.setBusinessId(type);
                tbSysFileService.insertOrUpdate(file);
                FileUtil.downloadForWeb(getRequest(), getResponse(), localFile, fileName + fileType.getFileSuffix());
            }
        } catch (Exception e) {
            log.error("生成数据库文档失败", e);
        } finally {
            if (StringUtil.isNotEmpty(filePath)) {
                FileUtil.deleteFile(filePath);
            }
        }
    }
}
